package etc;

import org.junit.Before;
import org.junit.Test;
import java.sql.*;

public class PhoenixTest1 {
    Connection connection = null;
    PreparedStatement ps = null;
    ResultSet re = null;
    String sql = "";

    /**
     * 创建连接
     * @throws ClassNotFoundException
     * @throws SQLException
     */

    @Before
    public void createConnections() throws ClassNotFoundException, SQLException {
        //连接驱动
        Class.forName("org.apache.phoenix.jdbc.PhoenixDriver");
        //得到连接对象
        connection = DriverManager.getConnection("jdbc:phoenix:master:2181");

    }

    /**
     * 查询表
     * @throws SQLException
     */
    @Test
    public void queryData() throws SQLException {
        sql = "select * from persion";
        //根据连接对象得到发送器
        ps = connection.prepareStatement(sql);
        re = ps.executeQuery();
        while (re.next()) {
            System.out.println(re.getString("NAME"));
        }
        ps.close();
        connection.close();
        /**
         * +------+---------+------+
         * |  ID  |  NAME   | AGE  |
         * +------+---------+------+
         * | 100  | JE      | 29   |
         * | 101  | JENERY  | 19   |
         * | 102  | TOM     | 12   |
         */
    }

    /**
     * 创建表
     * @throws SQLException
     */
    @Test
    public void createTable() throws SQLException {
        sql = "create table if not exists user(id integer not null primary key,name varchar(20),age integer)";
        ps = connection.prepareStatement(sql);
        ps.execute();

        System.out.println("string " + sql);
        ps.close();
        connection.close();
        /**
         *   ID  |  NAME   | AGE  |
         * +------+---------+------+
         * | 100  | JE      | 29   |
         * | 101  | JENERY  | 19   |
         */
    }

    /**
     * 添加数据
     * @throws SQLException
     */
    @Test
    public void add() throws SQLException{
        //把persion表中age>13的数据添加到user表中
        sql = "upsert into user select * from persion where AGE >13 ";
        ps = connection.prepareStatement(sql);
        int i = ps.executeUpdate();
        connection.commit();
        System.out.println("string"+ i + "行");
        ps.close();
        connection.close();
        /**
         * |  ID  |  NAME   | AGE  |
         * +------+---------+------+
         * | 100  | JE      | 29   |
         * | 101  | JENERY  | 19   |
         * +------+---------+------
         */
    }

    /**
     * 删除数据
     * @throws SQLException
     */
    @Test
    public void deleteData()  throws SQLException{
        sql = "delete from user where AGE = 19 ";
        ps = connection.prepareStatement(sql);
         //把执行完的数据赋值给i
//        int i = ps.executeUpdate();
        connection.commit();
        //输出表内的行数
//        System.out.println("string" + i + "行");
        ps.close();
        connection.close();
        /**
         * |  ID  |  NAME   | AGE  |
         * +------+---------+------+
         * | 100  | JE      | 29   |
         */
    }

    /**
     * 更新数据
     * @throws SQLException
     */
    @Test
    public void upsertData() throws SQLException{
        sql = "upsert into user(ID ,NAME) values (?,?) ";
        //准备连接
        ps = connection.prepareStatement(sql);
        //修改数据
        ps.setInt(1,1);
        ps.setString(2,"JE");
        int i = ps.executeUpdate();
        connection.commit();
        System.out.println("string " + i +"行");
        ps.close();
        connection.close();
    }
    /**
     * |  ID  | NAME  |  AGE  |
     * +------+-------+-------+
     * | 1    | JE    | null  |
     * | 100  | JE    | 29    |
     * +------+-------+-------+
     */

}
